package com.fau.wm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.validation.ConstraintViolation;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.hibernate.Hibernate;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.wm.util.ExcelUtil;
import com.fau.wm.util.SystemConfig;
import com.fau.wm.entity.MedicalAdmin;
import com.fau.wm.entity.MedicalAllocationDetail;
import com.fau.wm.entity.MedicalInventoryDetail;
import com.fau.wm.entity.MedicalKeyValue;
import com.fau.wm.entity.MedicalMedicine;
import com.fau.wm.entity.MedicalMedicinePosition;
import com.fau.wm.entity.MedicalMedicineSort;
import com.fau.wm.entity.MedicalOrdersProduct;
import com.fau.wm.entity.MedicalRecallDetail;
import com.fau.wm.entity.MedicalStockoutWarn;
import com.fau.wm.entity.MedicalStorageDetail;
import com.fau.wm.service.IMedicineManagerService;
import com.fau.wm.util.ChineseTransformUtil;
import com.fau.wm.util.Page;
import com.fau.wm.util.ParamUtil;
import com.fau.wm.util.ValidatorUtil;

@Service
@Transactional(readOnly = true)
public class MedicineManagerService extends BaseService implements
		IMedicineManagerService {

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalMedicineSort> findMedSortList(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalMedicineSort where hospitalId=? ", null, admin.getEnterpriseId());
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public Boolean saveMedicineSort(MedicalMedicineSort medicineSort, MedicalAdmin admin) {
		medicineSort.setUserId(admin.getId());
		medicineSort.setHospitalId(admin.getEnterpriseId());
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(medicineSort);
		if(errorInfo.size()>0){
			return false;
		}else{
			return this.iBaseDao.saveObject(medicineSort);
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public boolean containChild(String id) {
		List<MedicalMedicineSort> medicineSortList = this.iBaseDao.getBySql("from MedicalMedicineSort a where a.pid=? ", null, id);
		if(null != medicineSortList && medicineSortList.size() > 0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delMedicineSort(MedicalMedicineSort medicineSort) {
		this.iBaseDao.deleteObject(medicineSort);
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveMedicine(MedicalMedicine medicine, MedicalAdmin admin) {
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(medicine);
		if(errorInfo.size()>0){
			return false;
		}else{
			medicine.setUserId(admin.getId());
			medicine.setHospitalId(admin.getEnterpriseId());
			MedicalMedicineSort medicineSort = (MedicalMedicineSort) this.iBaseDao.getObject(MedicalMedicineSort.class, medicine.getMedicineSortId());
			medicine.setMedicineSort(medicineSort);
			String pinyin = ChineseTransformUtil.firstSpell(medicine.getName());
			medicine.setPinYin(pinyin);
			return this.iBaseDao.saveObject(medicine);
		}
	}

	@Override
	public Page findMedicineByPage(MedicalMedicine medicine, Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalMedicine a where a.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalMedicine a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(medicine.getHospitalId());
		
		// 药品名称
		if(StringUtils.isNotBlank(medicine.getName())){
			countSql.append("and a.name like ? ");
			statusSql.append("and a.name like ? ");
			objectList.add("%"+medicine.getName()+"%");
		}
		// 药品编号
		if(StringUtils.isNotBlank(medicine.getSerialNum())){
			countSql.append("and a.serialNum = ? ");
			statusSql.append("and a.serialNum = ? ");
			objectList.add(medicine.getSerialNum());
		}
		// 生产厂家
		if(StringUtils.isNotBlank(medicine.getManufacturer())){
			countSql.append("and a.manufacturer like ? ");
			statusSql.append("and a.manufacturer like ? ");
			objectList.add("%"+medicine.getManufacturer()+"%");
		}
		
		statusSql.append("order by a.createTime desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		@SuppressWarnings("unchecked")
		List<MedicalMedicine> list = (List<MedicalMedicine>) page.getResult();
		for (MedicalMedicine medicalMedicine : list) {
			MedicalKeyValue keyValue = this.iBaseDao.getValueByKey(medicine.getHospitalId(), medicalMedicine.getMedicineType());
			if(null != keyValue){
				medicalMedicine.setMedicineTypeValue(keyValue.getKeyValue());
			}
		}
		return page;
	}
	
	@Override
	public Page findMedicineIsWarn(MedicalMedicine medicine, Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalMedicine a where a.hospitalId=? and a.warnNum is not null ");
		StringBuilder statusSql = new StringBuilder("from MedicalMedicine a where a.hospitalId=? and a.warnNum is not null ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(medicine.getHospitalId());
		
		statusSql.append("order by a.createTime desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		@SuppressWarnings("unchecked")
		List<MedicalMedicine> list = (List<MedicalMedicine>) page.getResult();
		for (MedicalMedicine medicalMedicine : list) {
			MedicalKeyValue keyValue = this.iBaseDao.getValueByKey(medicine.getHospitalId(), medicalMedicine.getUnit());
			if(null != keyValue){
				medicalMedicine.setUnitValue(keyValue.getKeyValue());
			}
		}
		return page;
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean delMedicine(MedicalMedicine medicine) {
		if(medicine.getId().indexOf(",") != -1){
			String[] ids = medicine.getId().split(",");
			for(String id : ids){
				@SuppressWarnings("unchecked")
				List<MedicalOrdersProduct> products = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.medicine.id=? ", null, id);
				@SuppressWarnings("unchecked")
				List<MedicalAllocationDetail> details = this.iBaseDao.getBySql("from MedicalAllocationDetail a where a.medicine.id=? ", null, id);
				@SuppressWarnings("unchecked")
				List<MedicalInventoryDetail> inventoryDetails = this.iBaseDao.getBySql("from MedicalInventoryDetail a where a.medicine.id=? ", null, id);
				@SuppressWarnings("unchecked")
				List<MedicalRecallDetail> recallDetails = this.iBaseDao.getBySql("from MedicalRecallDetail a where a.medicine.id=? ", null, id);
				@SuppressWarnings("unchecked")
				List<MedicalStorageDetail> storageDetails = this.iBaseDao.getBySql("from MedicalStorageDetail a where a.medicine.id=? ", null, id);
				if(products.size() == 0 && details.size() == 0 && inventoryDetails.size() == 0
						&& recallDetails.size() == 0 && storageDetails.size() == 0){
					this.iBaseDao.updateHql("delete from MedicalMedicine a where a.id=? ", id);
				}else{
					return false;
				}
			}
			return true;
		}else{
			@SuppressWarnings("unchecked")
			List<MedicalOrdersProduct> products = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.medicine.id=? ", null, medicine.getId());
			@SuppressWarnings("unchecked")
			List<MedicalAllocationDetail> details = this.iBaseDao.getBySql("from MedicalAllocationDetail a where a.medicine.id=? ", null, medicine.getId());
			@SuppressWarnings("unchecked")
			List<MedicalInventoryDetail> inventoryDetails = this.iBaseDao.getBySql("from MedicalInventoryDetail a where a.medicine.id=? ", null, medicine.getId());
			@SuppressWarnings("unchecked")
			List<MedicalRecallDetail> recallDetails = this.iBaseDao.getBySql("from MedicalRecallDetail a where a.medicine.id=? ", null, medicine.getId());
			@SuppressWarnings("unchecked")
			List<MedicalStorageDetail> storageDetails = this.iBaseDao.getBySql("from MedicalStorageDetail a where a.medicine.id=? ", null, medicine.getId());
			if(products.size() == 0 && details.size() == 0 && inventoryDetails.size() == 0
					&& recallDetails.size() == 0 && storageDetails.size() == 0){
				this.iBaseDao.deleteObject(medicine);
				return true;
			}else{
				return false;
			}
		}
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delMedicineWarnNum(MedicalMedicine medicine) {
		String[] ids = medicine.getId().split(",");
		for(String id : ids){
			this.iBaseDao.updateHql("update from MedicalMedicine a set a.warnNum=null where a.id=? ", id);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalMedicine> findMedicineByName(MedicalAdmin admin, String name) {
		return this.iBaseDao.getBySql("from MedicalMedicine a where a.hospitalId=? and a.pinYin like ? ", null, admin.getEnterpriseId(), name + "%");
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveEditMedicine(MedicalMedicine medicine) {
		MedicalMedicine bean = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, medicine.getId());
		bean.setSerialNum(medicine.getSerialNum());
		bean.setName(medicine.getName());
		bean.setMedicineType(medicine.getMedicineType());
		String pinyin = ChineseTransformUtil.firstSpell(medicine.getName());
		bean.setPinYin(pinyin);
		bean.setUnit(medicine.getUnit());
		bean.setAliases(medicine.getAliases());
		bean.setManufacturer(medicine.getManufacturer());
		bean.setValid(medicine.getValid());
		MedicalMedicineSort medicineSort = (MedicalMedicineSort) this.iBaseDao.getObject(MedicalMedicineSort.class, medicine.getMedicineSortId());
		bean.setMedicineSort(medicineSort);
		bean.setIndications(medicine.getIndications());
		bean.setDosage(medicine.getDosage());
		bean.setMainIngredient(medicine.getMainIngredient());
		bean.setAvoid(medicine.getAvoid());
		bean.setPrice(medicine.getPrice());
		bean.setUpdateTime(new Date());
		this.iBaseDao.saveObject(bean);
	}

	@Override
	public Boolean verSerialNum(String serialNum) {
		MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObjectByHql("from MedicalMedicine a where a.serialNum=? ", serialNum);
		if(null == medicine){
			return true;
		}else{
			return false;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveEditMedicineSort(MedicalMedicineSort medicineSort) {
		MedicalMedicineSort bean = (MedicalMedicineSort) this.iBaseDao.getObject(MedicalMedicineSort.class, medicineSort.getId());
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateProperty(medicineSort, "name");
		if(errorInfo.size() > 0){
			return false;
		}else{
			bean.setName(medicineSort.getName());
			bean.setUpdateTime(new Date());
			return true;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public Boolean checkSortInUse(String id) {
		List<MedicalMedicine> medicineList = this.iBaseDao.getBySql("from MedicalMedicine a where a.medicineSort.id=? ", null, id);
		if(null != medicineList && medicineList.size() > 0) {
			return true;
		}else {
			return false;
		}
	}

	public MedicalMedicine findMedicineById(String id){
		MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, id);
		Hibernate.initialize(medicine.getMedicineSort());
		MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, medicine.getUserId());
		medicine.setUserName(admin.getName());
		return medicine;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importMedicine(MedicalAdmin admin, File file) throws Exception {
		String info = "true";
		
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		// 循环逐行导入
		while(rows.hasNext()) {
			row = rows.next();
			if(row.getRowNum() <= 1) continue;
			// 查询药品类别是否存在，不存在提示先维护类别
			if(null != row.getCell(5)) {
				MedicalMedicineSort medicineSort = (MedicalMedicineSort) this.iBaseDao.getObjectByHql("from MedicalMedicineSort a where a.name=? ", getValue(row.getCell(5)).trim());
				if(null != medicineSort) {
					
					if(null != row.getCell(12)) {
						MedicalKeyValue medicineType = (MedicalKeyValue) this.iBaseDao.getObjectByHql("from MedicalKeyValue a where a.keyValue=? and a.hospitalId=? ", getValue(row.getCell(12)).trim(),user.getEnterpriseId());
						if(null != medicineType) {
							
							MedicalMedicine medicine = new MedicalMedicine();
							medicine.setMedicineType(medicineType.getKeyName());
							if(null != getValue(row.getCell(0))) {
								medicine.setSerialNum(getValue(row.getCell(0)).trim());
							}
							if(null != getValue(row.getCell(1))) {
								medicine.setName(getValue(row.getCell(1)).trim());
							}
							if(null != getValue(row.getCell(2))) {
								medicine.setAliases(getValue(row.getCell(2)).trim());
							}
							if(null != getValue(row.getCell(3))) {
								medicine.setManufacturer(getValue(row.getCell(3)).trim());
							}
							if(null != getValue(row.getCell(4))) {
								medicine.setValid(getValue(row.getCell(4)).trim());
							}
							medicine.setMedicineSort(medicineSort);
							MedicalKeyValue unitKeyVal = (MedicalKeyValue) this.iBaseDao.getObjectByHql("from MedicalKeyValue a where a.keyValue=? and a.hospitalId=? ", getValue(row.getCell(6)).trim(),user.getEnterpriseId());
							if(null != unitKeyVal){
								medicine.setUnit(unitKeyVal.getKeyName());
							}
							System.out.println();
							if(null != row.getCell(7)){
								medicine.setIndications(getValue(row.getCell(7)).trim());
							}
							if(null != row.getCell(8)){
								medicine.setDosage(getValue(row.getCell(8)).trim());
							}
							if(null != row.getCell(9)){
								medicine.setMainIngredient(getValue(row.getCell(9)).trim());
							}
							if(null != row.getCell(10)){
								medicine.setAvoid(getValue(row.getCell(10)).trim());
							}
							if(null != row.getCell(11)) {
								medicine.setPrice(new BigDecimal(getValue(row.getCell(11)).trim()));
							}
							
							medicine.setHospitalId(admin.getEnterpriseId());
							medicine.setUserId(admin.getId());
							Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(medicine);
							if(errorInfo.size() > 0) {
								return "false";
							} else {
								this.iBaseDao.saveObject(medicine);
							}
						}
					} else {
						info = "第"+(row.getRowNum()+1)+"行药品类型不能为空";
					}
				} else {
					info = "第"+(row.getRowNum()+1)+"行药品类别不存在";
				}
			}
		}
		return info;
	}

	/**
	 * 列值类型的转换
	 * @author dangjh
	 * @param cell
	 * @return
	 * 2014年3月13日 下午5:56:51
	 */
	private static String getValue(Cell cell) {  
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
            return String.valueOf(cell.getBooleanCellValue());  
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        	String cellValue = String.valueOf(cell.getNumericCellValue());
        	if(cellValue.contains(".")){
        		cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
        	}
            return cellValue;  
        } else {
        	cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());  
        }  
    }
	
	@SuppressWarnings("unchecked")
	@Override
	public boolean isRepeatName(String pid, String name) {
		List<MedicalMedicineSort> list = this.iBaseDao.getBySql("from MedicalMedicineSort a where a.pid=? and a.name=? ", null, pid,name);
		if(list.size() > 0){
			return true;
		}else{
			return false;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public boolean findMedicineByName(String name) {
		List<MedicalMedicine> list = this.iBaseDao.getBySql("from MedicalMedicine a where a.name=? ", null, name);
		if (list.size() > 0) {
			return true;
		}else{
			return false;
		}
	}

	@Override
	public int findMedicineWarnNum() {
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		MedicalStockoutWarn stockoutWarn = (MedicalStockoutWarn) this.iBaseDao.getObjectByHql("from MedicalStockoutWarn a where a.eid=? ",admin.getEnterpriseId());
		int i = 0;
		if(null != stockoutWarn){
			i = stockoutWarn.getNum();
		}
		return i;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void setMedicineWarnNum(int num) {
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		this.iBaseDao.updateHql("delete from MedicalStockoutWarn a where a.eid=? ",admin.getEnterpriseId());
		MedicalStockoutWarn stockoutWarn = new MedicalStockoutWarn();
		stockoutWarn.setNum(num);
		stockoutWarn.setEid(admin.getEnterpriseId());
		this.iBaseDao.saveObject(stockoutWarn);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void setMedicineWarnNumSpe(MedicalMedicine medicine) {
		this.iBaseDao.updateHql("update MedicalMedicine a set a.warnNum=? where a.id=? ", medicine.getWarnNum(),medicine.getId());
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateMedicineWarnNumSpe(MedicalMedicine medicine) {
		this.iBaseDao.updateHql("update MedicalMedicine a set a.warnNum=? where a.id=? ", medicine.getWarnNum(),medicine.getId());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalMedicinePosition> findMedicalLocation(String mid) {
		List<MedicalMedicinePosition> list = this.iBaseDao.getBySql("from MedicalMedicinePosition a where a.medicine.id=? ", null, mid);
		return list;
	}
	
	@Override
	public void delExcel(String filePath) {
		File file = new File(filePath);
		if(file.isFile() && file.canWrite()){
			file.delete();
		}
	}
	
	@SuppressWarnings({ "unchecked" })
	@Override
	public Workbook downloadMedicineTemp(MedicalAdmin admin) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.MEDICINE_MASTERPLATE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		//药品类别
		List<MedicalMedicineSort> meSortList = this.iBaseDao.getBySql("from MedicalMedicineSort a where a.hospitalId = ? and a.pid is not null", null, admin.getEnterpriseId());
		List<String> medicineSortList = new ArrayList<String>();
		for(MedicalMedicineSort medicineSort : meSortList){
			medicineSortList.add(medicineSort.getName());
		}
		String[] medicineSorts = medicineSortList.toArray(new String[meSortList.size()]);
		
		// 药品类型
		List<MedicalKeyValue> keyValList = this.iBaseDao.findValueByType(admin.getEnterpriseId(), SystemConfig.WM_SYSTEM_TYPE, SystemConfig.MEDICINE_TYPE);
		List<String> medicineTypeList = new ArrayList<String>();
		for(MedicalKeyValue medicineType : keyValList){
			medicineTypeList.add(medicineType.getKeyValue());
		}
		String[] medicineTypes = medicineTypeList.toArray(new String[medicineTypeList.size()]);
		
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列   
		sheet = ExcelUtil.setPullDown(sheet, medicineTypes, 2, 500, 12, 12);	// 第一列的前501行都设置为选择列表形式.
		sheet = ExcelUtil.setPullDown(sheet, medicineSorts, 2, 500, 5, 5);
		return workbook;
	}
}
